﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class CTPhieuDatDAO
    {
        public string LayMaCTPDLonNhat()
        {
            string sql = "SELECT TOP  1, MACTPD FROM CTPHIEUDAT  ORDER BY MACTPD DESC  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            CTPhieuDatDTO ctpdDTO = new CTPhieuDatDTO();
            while (reader.Read())
            {
                ctpdDTO.MaCTPD = reader.GetString(0);

            }
            conn.Close();
            return ctpdDTO.MaCTPD;
        }

        public string NextID(string lastID, string prefixID)
        {
            if (lastID == null)
            {
                return prefixID + "001";
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumberID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumberID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumberID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;
        }
        public string TaoCTDHMoi()
        {
            string LastID = LayMaCTPDLonNhat();
            return NextID(LastID, "CTPD");
        }
        public int ThemCTPhieuDat(CTPhieuDatDTO ctpdDTO)
        {
             OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO CTPHIEUDAT(MaCTPD,MaPD,MaSP,SLDat,DonGia,ThanhTien) ";
            sql = sql + " VALUES(@MaCTPD,@MaPD,@MaSP,@SLDat,@DonGia,@ThanhTien) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaCTPD", OleDbType.VarChar);
            para.Value = ctpdDTO.MaCTPD;
            para = cmd.Parameters.Add("@MaPD", OleDbType.VarChar);
            para.Value = ctpdDTO.MaPD;
            para = cmd.Parameters.Add("@MaSP", OleDbType.VarChar);
            para.Value = ctpdDTO.MaSP;
            para = cmd.Parameters.Add("@SLDat", OleDbType.Integer);
            para.Value = ctpdDTO.SLDat;
            para = cmd.Parameters.Add("@DonGia", OleDbType.Double);
            para.Value = ctpdDTO.DonGia;
            para = cmd.Parameters.Add("@ThanhTien", OleDbType.Double);
            para.Value = ctpdDTO.ThanhTien;
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res >= 0)
                return 1; // thành công
            return 0; // không thành công
            
        }
 
        public List<CTPhieuDatDTO> TheoDoiDatHang(string MaPD)
        {
            string sql = "SELECT MaSP,SLDat,ThanhTien FROM CTPHIEUDAT  WHERE MaPD = '"+MaPD+"'"; 
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuDatDTO> DanhSach = new List<CTPhieuDatDTO>();
         
            while (reader.Read())
            {
                if(reader.IsDBNull(0) || reader.IsDBNull(1)|| reader.IsDBNull(2))
                    DanhSach = null;
                else
                {
                    CTPhieuDatDTO ctpdDTO = new CTPhieuDatDTO();
                    ctpdDTO.MaSP = reader.GetString(0);
                    ctpdDTO.SLDat = reader.GetInt32(1);
                    ctpdDTO.ThanhTien = reader.GetDouble(2);
                    DanhSach.Add(ctpdDTO);
                }  
            }
            conn.Close();
            return DanhSach;
            
        }
        public int LaySoLuongDatHang(string MaSP, string MaDH)
        {
            string sql = "SELECT SLDat FROM CTPHIEUDAT WHERE MaSP='" + MaSP + "' AND MaPD='" + MaDH + "'"; // choi dep
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();
            int res= reader.GetInt32(0); // kiem tra gia tri tra ve la null
            conn.Close();
            return res;
        }
        public int CapNhatSoLuongDat(int soluong, string MaSP)
        {
            string sql = "UPDATE CTPHIEUDAT SET SLDat=" + soluong + " WHERE MaSP= '" + MaSP + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        public List<CTPhieuDatDTO> LayMaSPTheoMaPD(string MaPD)
        {
            string sql = "SELECT masp FROM CTPHieuDat WHERE MaPD='"+MaPD+"'"; 
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuDatDTO> DanhSach = new List<CTPhieuDatDTO>();

            while (reader.Read())
            {
                CTPhieuDatDTO ctpDTO = new CTPhieuDatDTO();
                ctpDTO.MaSP = reader.GetString(0);
                DanhSach.Add(ctpDTO);
            }
            conn.Close();
            return DanhSach;
        }
        public List<CTPhieuDatDTO> LayPhieuDatChuaNhap(string MaPD)
        {
            string sql = "SELECT MaSP,SLDat FROM CTPHIEUDAT  WHERE MaPD = '"+MaPD+"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuDatDTO> DanhSach = new List<CTPhieuDatDTO>();

            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) )
                    DanhSach = null;
                else
                {
                    CTPhieuDatDTO ctpdDTO = new CTPhieuDatDTO();
                    ctpdDTO.MaSP = reader.GetString(0);
                    ctpdDTO.SLDat = reader.GetInt32(1);
                    DanhSach.Add(ctpdDTO);
                }
            }
            conn.Close();
            return DanhSach;
        }
    }

}
